package work.getter;


import javafx.util.Pair;
import lombok.var;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import work.config.Constant;
import work.fram.JDBCConnect;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class TableFiledMapping {

    private static Map<Pair<String, String>, Map<String, String>> pairToMap;

    private static Map<String, Map<String, String>> nameToMap;

    private static Map<String, Map<String, String>> codeToMap;

    static {

        try {
            pairToMap = readMap();
        } catch (SQLException e) {
            throw new Error(e);
        }

        if (pairToMap == null) {
            throw new RuntimeException("无法处理Excel文件");
        }

        nameToMap = new HashMap<>();
        codeToMap = new HashMap<>();

        for (Pair<String, String> pair : pairToMap.keySet()) {
            Map<String, String> map = pairToMap.get(pair);
            codeToMap.put(pair.getKey(), map);
            nameToMap.put(pair.getValue(), map);
        }
    }


    private static Map<Pair<String, String>, Map<String, String>> readMap() throws SQLException {


        String s =
                        "主表\ttc_md_personnel\n" +
                        "合同信息\ttc_md_personnel_contract\n" +
                        "学历信息\ttc_md_personnel_ducation\n" +
                        "家庭信息\ttc_md_personnel_family\n" +
                        "出国（出境）情况信息\ttc_md_personnel_goabroad_situation\n" +
                        "语言能力信息\ttc_md_personnel_linguistic_competence\n" +
                        "兼职借用信息\ttc_md_personnel_parttime_loan\n" +
                        "参加党派记录信息\ttc_md_personnel_partygroups_record\n" +
                        "任职信息信息\ttc_md_personnel_post_information\n" +
                        "执业资格信息\ttc_md_personnel_practis_qualification\n" +
                        "处分情况信息\ttc_md_personnel_punishment_situation\n" +
                        "履历记录信息\ttc_md_personnel_resume_record\n" +
                        "奖励情况信息\ttc_md_personnel_reward_situation\n" +
                        "班子情况信息\ttc_md_personnel_team_situation\n" +
                        "专业技术职务信息\ttc_md_personnel_technical_position\n" +
                        "培训情况信息\ttc_md_personnel_training_situation\n" +
                        "工人技术等级信息\ttc_md_personnel_workers_technical_level\n";


        String[] split = s.split("\n");

        Statement statement = JDBCConnect.getStatement();

        Map<Pair<String, String>, Map<String, String>> pairMapMap = new HashMap<>();

        for (String line : split) {

            String[] strings = line.split("\t");


            //表中文名称
            String name = strings[0];

            //表名
            String code = strings[1];

            ResultSet resultSet = statement.executeQuery("SELECT\n" +
                    "    COLUMN_NAME columnName,\n" +
                    "    COLUMN_COMMENT COMMENT\n" +
                    "FROM\n" +
                    "  INFORMATION_SCHEMA.COLUMNS\n" +
                    "WHERE\n" +
                    "  table_schema = 'mdm'\n" +
                    "AND\n" +
                    "  table_name = '" + code + "'");

            Map<String, String> map = new HashMap<>();

            while (resultSet.next()) {

                //字段名
                String a = resultSet.getString(1);

                //字段中文名称
                String b = resultSet.getString(2);

                //放入字段映射
                map.put(b, a);
            }

            map.remove("主键");
            map.remove("主数据统一编码");
            map.remove("主数据统一编码");

            //放入性别映射


            Pair<String, String> pair = new Pair<>(code, name);
            pairMapMap.put(pair, map);


        }

        return pairMapMap;
    }


    public static void main(String[] args) {


        Map<String, String> map = getMapByName("主表");
        System.out.println(map);

    }

    public static Map<String, Map<String, String>> getNameToMap() {
        return nameToMap;
    }

    public static Map<String, Map<String, String>> getCodeToMap() {
        return codeToMap;
    }

    //才有包含的方式返回
    public static Map<String, String> getMapByName(String name) {

        //采用模糊匹配的方式

        for (String n : nameToMap.keySet()) {
            if (n.contains(name)) {
                return nameToMap.get(name);
            }
        }
        return null;
    }

    public static String getNameByCode(String code) {
        for (Pair<String, String> pair : pairToMap.keySet()) {
            if (pair.getKey().equals(code)) {
                return pair.getValue();
            }
        }
        return null;
    }

    public static String getCodeByName(String name) {
        for (Pair<String, String> pair : pairToMap.keySet()) {
            if (pair.getValue().contains(name)) {
                return pair.getKey();
            }
        }
        return null;
    }

    public static Map<String, String> getMapByCode(String code) {
        return nameToMap.get(code);
    }


    public static Set<String> getNames() {
        return nameToMap.keySet();
    }

    public static Set<String> getCodes() {
        return codeToMap.keySet();
    }

}
